﻿CREATE PROCEDURE [dbo].[usp_select_Faults]
(
	@FaultID			varchar(50)		 = NULL
   ,@Application		varchar(50)      = NULL   
   ,@StartDate			varchar(25)      = NULL   
   ,@EndDate			varchar(25)      = NULL   
   ,@FaultCode			varchar(50)      = NULL   
   ,@FailureCategory	varchar(50)      = NULL
   ,@ErrorType			varchar(50)      = NULL 
   ,@MaxFaultSeverity	int				 = 4
   ,@StartRowIndex		int
   ,@MaximumRows		int
   ,@SortColumn			varchar(50)		 = ''
   ,@Debug              bit              = 0
)
AS

SET NOCOUNT ON;

-- Bootstrap
DECLARE @DebugPrefix       varchar(10)
DECLARE @ErrorCode         int

SET @ErrorCode          = 0;
SET @DebugPrefix        = '>>> DEBUG: '


-- declare the stored procedure parameters
/*
DECLARE @FaultID		   varchar(50);
DECLARE @Application       varchar(50);
DECLARE @StartDate         datetime;
DECLARE @EndDate           datetime;
DECLARE @FaultCode         varchar(50);
DECLARE @FailureCategory   varchar(50);
DECLARE @ExceptionType     varchar(50);
DECLARE @MinFaultSeverity  int;
DECLARE @MaxFaultSeverity  int;

DECLARE @Debug             bit;
-- now set them
SELECT
	@FaultID			   = NULL
   ,@Application           = NULL    	
   ,@StartDate             = NULL    	
   ,@EndDate               = NULL    
   ,@FaultCode             = NULL       
   ,@FailureCategory       = NULL
   ,@ExceptionType         = NULL 
   ,@MinFaultSeverity      = NULL
   ,@MaxFaultSeverity      = NULL
   ,@Debug                 = 1;
*/

-- Print parameters
IF (@Debug > 0)
BEGIN
   PRINT @DebugPrefix + '@FaultID = ' + @FaultID
   PRINT @DebugPrefix + '@Application = ' + @Application
   PRINT @DebugPrefix + '@StartDate = ' + CAST(@StartDate AS varchar)
   PRINT @DebugPrefix + '@EndDate = ' + CAST(@EndDate AS varchar)
   PRINT @DebugPrefix + '@FaultCode = ' + @FaultCode
   PRINT @DebugPrefix + '@FailureCategory = ' + @FailureCategory
   PRINT @DebugPrefix + '@ErrorType = ' + @ErrorType
END;


---------------------------------------------------------------
--	Select the faults.
---------------------------------------------------------------

IF @SortColumn = '' BEGIN
	SET @SortColumn = 'DateTime'
END

DECLARE @SqlStatement nvarchar(max)
SET @SqlStatement = N' SELECT
  FaultsWithRowNum.[FaultID]
  ,FaultsWithRowNum.[NativeMessageID]
  ,FaultsWithRowNum.[ActivityID]
  ,FaultsWithRowNum.[Application]
  ,FaultsWithRowNum.[Description]
  ,FaultsWithRowNum.[ErrorType]
  ,FaultsWithRowNum.[FailureCategory]
  ,FaultsWithRowNum.[FaultCode]
  ,FaultsWithRowNum.[FaultDescription]
  ,FaultsWithRowNum.[FaultSeverity]
  ,FaultsWithRowNum.[Scope]
  ,FaultsWithRowNum.[ServiceInstanceID]
  ,FaultsWithRowNum.[ServiceName]
  ,FaultsWithRowNum.[FaultGenerator]
  ,FaultsWithRowNum.[MachineName]
  ,FaultsWithRowNum.[DateTime]
  ,FaultsWithRowNum.[ExceptionMessage]
  ,FaultsWithRowNum.[ExceptionType]
  ,FaultsWithRowNum.[ExceptionSource]
  ,FaultsWithRowNum.[ExceptionTargetSite]
  ,FaultsWithRowNum.[ExceptionStackTrace]
  ,FaultsWithRowNum.[InnerExceptionMessage]

FROM
(
	SELECT 
	   ROW_NUMBER() OVER (ORDER BY ' + @SortColumn + N') AS Row
	  ,f.[FaultID]
	  ,f.[NativeMessageID]
	  ,f.[ActivityID]
	  ,f.[Application]
	  ,f.[Description]
	  ,f.[ErrorType]
	  ,f.[FailureCategory]
	  ,f.[FaultCode]
	  ,f.[FaultDescription]
	  ,f.[FaultSeverity]
	  ,f.[Scope]
	  ,f.[ServiceInstanceID]
	  ,f.[ServiceName]
	  ,f.[FaultGenerator]
	  ,f.[MachineName]
	  ,f.[DateTime]
	  ,f.[ExceptionMessage]
	  ,f.[ExceptionType]
	  ,f.[ExceptionSource]
	  ,f.[ExceptionTargetSite]
	  ,f.[ExceptionStackTrace]
	  ,f.[InnerExceptionMessage]

	FROM dbo.Fault f 
	WHERE 
			 f.[FaultID] = ' + CASE WHEN @FaultID IS NULL THEN N'f.FaultID' ELSE N'''' + @FaultID + N'''' END + N'
	   AND   f.[Application] = ' + CASE WHEN @Application IS NULL THEN N'f.[Application]' ELSE N'''' + @Application + N'''' END + N'
	   AND   f.[FailureCategory] = ' + CASE WHEN @FailureCategory IS NULL THEN N'f.FailureCategory' ELSE N'''' + @FailureCategory + N'''' END + N'
	   AND   f.ErrorType = ' + CASE WHEN @ErrorType IS NULL THEN N'f.ErrorType' ELSE N'''' + @ErrorType + N'''' END + N'
	   AND   f.[FaultCode] =  ' + CASE WHEN @FaultCode IS NULL THEN N'f.[FaultCode]' ELSE N'''' + @FaultCode + N'''' END + N'
	   AND   (dbo.udf_GetDateAtMidnight(f.[DateTime]) >= ' + CASE WHEN @StartDate = '-1' THEN N'dbo.udf_GetDateAtMidnight(f.[DateTime])' ELSE 'dbo.udf_GetDateAtMidnight(''' + @StartDate + ''')' END  + N' AND dbo.udf_GetDateAtMidnight(f.[DateTime]) <= ' + CASE WHEN @EndDate = '-1' THEN N'dbo.udf_GetDateAtMidnight(f.[DateTime])' ELSE 'dbo.udf_GetDateAtMidnight(''' + @EndDate + ''')' END + N')
	   AND   f.[FaultSeverity] between 0 AND ' + cast(@MaxFaultSeverity as varchar(20)) + N'
	
) AS FaultsWithRowNum

WHERE 
	FaultsWithRowNum.Row >= (' + CONVERT(nvarchar(3), @StartRowIndex) + N' + 1)
	AND FaultsWithRowNum.Row < (' + CONVERT(nvarchar(3), @StartRowIndex) + N' + ' + CONVERT(nvarchar(3), @MaximumRows) + N' + 1)'

PRINT @SqlStatement
EXEC (@SqlStatement)




---------------------------------------------------------------
--	Select the total row count.
---------------------------------------------------------------
/*
SELECT
	@TotalRows = COUNT(FaultID)

FROM dbo.Fault f 

WHERE 
		 f.[FaultID] = ISNULL(@FaultID, f.FaultID)
   AND   f.[Application] = ISNULL(@Application, f.[Application])
   AND   f.[FailureCategory] = ISNULL(@FailureCategory, f.FailureCategory)
   AND   f.ExceptionType = ISNULL(@ExceptionType, f.ExceptionType)
   AND   f.[FaultCode] = ISNULL(@FaultCode, f.[FaultCode])
   AND   (
			f.[DateTime] between CASE WHEN @StartDate IS NULL THEN f.[DateTime] ELSE CONVERT(varchar(25), @StartDate) END 
			AND CASE WHEN @EndDate IS NULL THEN f.[DateTime] ELSE CONVERT(varchar(25), @EndDate) END
		)
   AND   (f.[FaultSeverity] between ISNULL(@MinFaultSeverity, f.[FaultSeverity]) AND ISNULL(@MaxFaultSeverity, f.[FaultSeverity]))

*/


-- CHECK FOR ERROR
SET @ErrorCode = @@Error;
IF (@ErrorCode <> 0) GOTO ERROR_HANDLER;
			
RETURN 0;

-- Handles errors.
ERROR_HANDLER:
IF (@ErrorCode <> 0)
BEGIN
    IF (@Debug > 0) 
		BEGIN
			PRINT @DebugPrefix + 'Error Code = ' + CAST(@ErrorCode AS varchar(10)) + ' occurred.';
			RETURN @ErrorCode
		END

    -- Don't return any evidence of what specific error occurred, i.e. 
    -- we want to ward off potential hackers.
    RETURN 1;
END;

SET NOCOUNT OFF;
